--    Program: 		CAPPOEXT_POSITION_HIERARCHY.sql
--    Description:	Extract PO Position Hierarchy information 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
  l_count				   number;
  l_processed_count_s 	   number;
  l_rejected_count_s  	   number;
  l_processed_count_r	   number;
  l_rejected_count_r	   number;
  l_processed_count_l	   number;
  l_rejected_count_l	   number;
  l_err_msg       		   varchar2(2000);
  
  l_utl_file_1  	       utl_file.file_type;
  l_utl_file_2 		       utl_file.file_type;
  l_utl_file_3 		       utl_file.file_type;
  l_filename_1 		       varchar2(255);
  l_filename_2 		       varchar2(255);
  l_filename_3 		       varchar2(255);
  l_pathname  		       varchar2(100);
  
  l_set_of_books_name	   varchar2(150);
  l_set_of_books_id		   number;

  l_application_id         number(15);
  
  cursor cur_pps  (p_set_of_books_name varchar2) is 
	select pps.* 
	from   PER_POSITION_STRUCTURES PPS, 
		   HR_ORGANIZATION_UNITS HOU
	where  pps.business_group_id = hou.business_group_id 
    and    upper(hou.name) = 'BG_'||upper(substr(P_SET_OF_BOOKS_NAME, -4));
	
  cursor cur_ppsv (P_BUSINESS_GROUP_ID number, P_POSITION_STRUCTURE_ID number) is
	select ppsv.*
	from   PER_POS_STRUCTURE_VERSIONS ppsv 
	where  ppsv.BUSINESS_GROUP_ID=P_BUSINESS_GROUP_ID and 
		   ppsv.POSITION_STRUCTURE_ID=P_POSITION_STRUCTURE_ID;
	
  cursor cur_ppse (P_BUSINESS_GROUP_ID number, P_POS_STRUCTURE_VERSION_ID number) is
	select ppse.*
	from   PER_POS_STRUCTURE_ELEMENTS ppse 
	where  ppse.BUSINESS_GROUP_ID=P_BUSINESS_GROUP_ID and 
		   ppse.POS_STRUCTURE_VERSION_ID=P_POS_STRUCTURE_VERSION_ID;


begin
  l_pathname := '/tmp';
  l_filename_1 := 'CAPPO_POSITION_HIERARCHY_STRUCTURE.dat';
  l_filename_2 := 'CAPPO_POSITION_HIERARCHY_VERSION.dat';
  l_filename_3 := 'CAPPO_POSITION_HIERARCHY_ELEMENT.dat';
  
  l_utl_file_1 := utl_file.fopen(l_pathname, l_filename_1, 'w', 32767);
  l_utl_file_2 := utl_file.fopen(l_pathname, l_filename_2, 'w', 32767);
  l_utl_file_3 := utl_file.fopen(l_pathname, l_filename_3, 'w', 32767);
  
  l_count := 0;
  l_processed_count_s := 0;
  l_rejected_count_s  := 0;
  l_processed_count_r := 0;
  l_rejected_count_r  := 0;
  l_processed_count_l := 0;
  l_rejected_count_l  := 0;
                                         
  l_set_of_books_name := '&1';
  
  begin 
    select set_of_books_id 
	into   l_set_of_books_id 
	from   gl_sets_of_books 
	where  upper(short_name) = upper(l_set_of_books_name);
  exception when others then
   dbms_output.put_line('set_of_books_mame '||l_set_of_books_name|| ' not found');
  end;	
  
  for s_pps in cur_pps(l_set_of_books_name) loop 
   utl_file.put_line( l_utl_file_1, 
	  s_pps.POSITION_STRUCTURE_ID|| chr(9) ||
	  s_pps.BUSINESS_GROUP_ID|| chr(9) ||
	  s_pps.NAME|| chr(9) ||
	  s_pps.COMMENTS|| chr(9) ||
	  s_pps.PRIMARY_POSITION_FLAG|| chr(9) ||
	  s_pps.REQUEST_ID|| chr(9) ||
	  s_pps.PROGRAM_APPLICATION_ID|| chr(9) ||
	  s_pps.PROGRAM_ID|| chr(9) ||
	  s_pps.PROGRAM_UPDATE_DATE|| chr(9) ||
	  s_pps.ATTRIBUTE_CATEGORY|| chr(9) ||
	  s_pps.ATTRIBUTE1|| chr(9) ||
	  s_pps.ATTRIBUTE2|| chr(9) ||
	  s_pps.ATTRIBUTE3|| chr(9) ||
	  s_pps.ATTRIBUTE4|| chr(9) ||
	  s_pps.ATTRIBUTE5|| chr(9) ||
	  s_pps.ATTRIBUTE6|| chr(9) ||
	  s_pps.ATTRIBUTE7|| chr(9) ||
	  s_pps.ATTRIBUTE8|| chr(9) ||
	  s_pps.ATTRIBUTE9|| chr(9) ||
	  s_pps.ATTRIBUTE10|| chr(9) ||
	  s_pps.ATTRIBUTE11|| chr(9) ||
	  s_pps.ATTRIBUTE12|| chr(9) ||
	  s_pps.ATTRIBUTE13|| chr(9) ||
	  s_pps.ATTRIBUTE14|| chr(9) ||
	  s_pps.ATTRIBUTE15|| chr(9) ||
	  s_pps.ATTRIBUTE16|| chr(9) ||
	  s_pps.ATTRIBUTE17|| chr(9) ||
	  s_pps.ATTRIBUTE18|| chr(9) ||
	  s_pps.ATTRIBUTE19|| chr(9) ||
	  s_pps.ATTRIBUTE20|| chr(9) ||
	  s_pps.LAST_UPDATE_DATE|| chr(9) ||
	  s_pps.LAST_UPDATED_BY|| chr(9) ||
	  s_pps.LAST_UPDATE_LOGIN|| chr(9) ||
	  s_pps.CREATED_BY|| chr(9) ||
	  s_pps.CREATION_DATE|| chr(9) ||
	  s_pps.OBJECT_VERSION_NUMBER
    );
	l_processed_count_s := l_processed_count_s + 1;  
	
      for s_ppsv in cur_ppsv(s_pps.BUSINESS_GROUP_ID , s_pps.POSITION_STRUCTURE_ID) loop
      
        utl_file.put_line( l_utl_file_2,        
          s_ppsv.POS_STRUCTURE_VERSION_ID|| chr(9) ||
	       s_ppsv.BUSINESS_GROUP_ID|| chr(9) ||
	       s_ppsv.POSITION_STRUCTURE_ID|| chr(9) ||
	       s_ppsv.DATE_FROM|| chr(9) ||
	       s_ppsv.VERSION_NUMBER|| chr(9) ||
	       s_ppsv.COPY_STRUCTURE_VERSION_ID|| chr(9) ||
	       s_ppsv.DATE_TO|| chr(9) ||
	       s_ppsv.REQUEST_ID|| chr(9) ||
	       s_ppsv.PROGRAM_APPLICATION_ID|| chr(9) ||
	       s_ppsv.PROGRAM_ID|| chr(9) ||
	       s_ppsv.PROGRAM_UPDATE_DATE|| chr(9) ||
	       s_ppsv.LAST_UPDATE_DATE|| chr(9) ||
	       s_ppsv.LAST_UPDATED_BY|| chr(9) ||
	       s_ppsv.LAST_UPDATE_LOGIN|| chr(9) ||
	       s_ppsv.CREATED_BY|| chr(9) ||
	       s_ppsv.CREATION_DATE|| chr(9) ||
	       s_ppsv.OBJECT_VERSION_NUMBER
        );
        l_processed_count_r := l_processed_count_r + 1;
	
	    for s_ppse in cur_ppse(s_ppsv.BUSINESS_GROUP_ID , s_ppsv.POS_STRUCTURE_VERSION_ID) loop
        
          utl_file.put_line( l_utl_file_3,           
            s_ppse.POS_STRUCTURE_ELEMENT_ID|| chr(9) ||
	         s_ppse.BUSINESS_GROUP_ID|| chr(9) ||
	         s_ppse.POS_STRUCTURE_VERSION_ID|| chr(9) ||
	         s_ppse.SUBORDINATE_POSITION_ID|| chr(9) ||
	         s_ppse.PARENT_POSITION_ID|| chr(9) ||
	         s_ppse.REQUEST_ID|| chr(9) ||
	         s_ppse.PROGRAM_APPLICATION_ID|| chr(9) ||
	         s_ppse.PROGRAM_ID|| chr(9) ||
	         s_ppse.PROGRAM_UPDATE_DATE|| chr(9) ||
	         s_ppse.LAST_UPDATE_DATE|| chr(9) ||
	         s_ppse.LAST_UPDATED_BY|| chr(9) ||
	         s_ppse.LAST_UPDATE_LOGIN|| chr(9) ||
	         s_ppse.CREATED_BY|| chr(9) ||
	         s_ppse.CREATION_DATE|| chr(9) ||
	         s_ppse.OBJECT_VERSION_NUMBER	      
          );
	    
          l_processed_count_l := l_processed_count_l + 1;
	    end loop;
      end loop;
  end loop;
  
  l_err_msg := '' ;
  if l_processed_count_s = 0 then
     l_rejected_count_s := l_rejected_count_s + 1;
     l_err_msg := l_set_of_books_name||': Position Hierarchy Structure not found' ;
  end if;
  
  insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
  values ( 'CAPPOEXT_POSITION_HIERARCHY_STRUCTURE', sysdate, l_processed_count_s, l_rejected_count_s, l_processed_count_s-l_rejected_count_s, l_err_msg );
  
  l_err_msg := '' ;
  if l_processed_count_r = 0 then
     l_rejected_count_r := l_rejected_count_r + 1;
     l_err_msg := l_set_of_books_name||': Position Hierarchy Version not found' ;
  end if;
  
  insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
  values ( 'CAPPOEXT_POSITION_HIERARCHY_VERSION', sysdate, l_processed_count_r, l_rejected_count_r, l_processed_count_r-l_rejected_count_r, l_err_msg );
  
  l_err_msg := '' ;
  if l_processed_count_l = 0 then
     l_rejected_count_l := l_rejected_count_l + 1;
     l_err_msg := l_set_of_books_name||': Position Hierarchy Element not found' ;
  end if;
  
  insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
  values ( 'CAPPOEXT_POSITION_HIERARCHY_ELEMENT', sysdate, l_processed_count_l, l_rejected_count_l, l_processed_count_l-l_rejected_count_l, l_err_msg );
  	    
  commit;
  
  utl_file.fclose(l_utl_file_1);
  utl_file.fclose(l_utl_file_2);
  utl_file.fclose(l_utl_file_3);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;
end;
/

--exit
